﻿using System;
using System.Linq;
using System.Data;

namespace SquidConsult.DAL
{
    public class AccountantDS : PeopleDS
    {
        #region Get reports
        public IQueryable GetAllReport()
        {
            var reports = from rep in context.reports
                          join con in context.consultants on rep.ConsultantId equals con.ConsultantId
                          where rep.Status_Supervisor == "Approved"
                          orderby rep.ReportDate
                          select new {
                                      rep.ReportId,
                                      con.ConsultantName,
                                      rep.ReportDate,
                                      rep.TotalExpense,
                                      rep.SupervisorName,
                                      rep.Status_Account
                               };
            return reports;
        }
        #endregion 

        #region Approved or Reject Reports
        public void UpdateStatus(Guid reportId, string status)
        {
            var rep = context.reports.Single(r => r.ReportId == reportId);

            rep.Status_Account = status;

            context.SaveChanges();
        }
        #endregion

        #region Get Company Budget
        public double GetCompanyTotalExpense()
        {
            //Get all approved reports
            var reports = from rep in context.reports
                          where rep.Status_Account == "Approved"
                          && rep.Status_Supervisor == "Approved"
                          select rep;

            if (reports.Count() == 0)
                return 0;
            else
                return double.Parse(reports.Sum(r => r.TotalExpense).ToString());
        }

        public double GetCompanyTotalBudget()
        {
            var deps = from dep in context.departments
                       select dep;

            return double.Parse(deps.Sum(d => d.Budget).ToString());        
        }
        #endregion

        #region Get Indiviadul supervisor TotalExpense
        /// <summary>
        /// Get total amount of expense approved to date 
        /// for each individul supervisor
        /// </summary>
        /// <param name="supervisorName"></param>
        /// <returns></returns>
        public double GetSupervisorTotalExpense(string supervisorName)
        {
            var reps = from rep in context.reports
                       where rep.SupervisorName == supervisorName
                       && rep.Status_Supervisor == "Approved"
                       && rep.Status_Account == "Approved"
                       select rep;

            if (reps.Count() == 0)
                return 0;
            else
                return double.Parse(reps.Sum(r => r.TotalExpense).ToString());
        }

        public IQueryable GetSupervisorName()
        {
            var sups = from sup in context.supervisors
                       select sup.SupervisorName;
            return sups;
        }
       #endregion 
    }
}
